
const xlsx = require('node-xlsx');
const fs = require('fs');
const path = require('path');
const mysql = require('mysql');
const progress = require('cli-progress');

const dbConfig = {
  host: '127.0.0.1',
  user: 'root',
  password: 'root',
  database: 'excel_import',
  connectTimeout: 20000,
  multipleStatements: true // 允许执行多条SQL语句
};

const _output = `${__dirname}/result/`;
const tableName = 'xlsxSet';
const splitNumber = 200000;

if (!fs.existsSync(_output)) {
  fs.mkdirSync(_output, { recursive: true });
}

function writeExcel (data, fileName) {
  console.time();
  const buffer = xlsx.build([{
    name: "Sheet1",
    data: [Object.keys(data[0])].concat(data.map(Object.values))
  }], {
    '!cols': Object.keys(data[0]).map(() => ({ wch: 20 }))
  });

  fs.writeFileSync(`${_output}${fileName}.xlsx`, buffer);
  console.log(`👌完成导出：${_output}${fileName}.xlsx (${data.length}条)`);
  console.timeEnd();
}

function exportAndDelete (callback, conn) {
  conn.query(`SELECT COUNT(*) AS total FROM ${tableName}`, (err, result) => {
    if (err) return callback(err);

    const total = result[0].total;
    const batchCount = Math.ceil(total / splitNumber);
    const bar = new progress.SingleBar({
      format: '处理进度 |{bar}| {percentage}% | {value}/{total} 批',
      barCompleteChar: '\u2588',
      barIncompleteChar: '\u2591'
    });
    bar.start(batchCount, 0);

    let currentBatch = 0;

    function processBatch () {
      const offset = currentBatch * splitNumber;
      conn.beginTransaction(err => {
        if (err) {
          bar.stop();
          return callback(err);
        }

        // 1. 查询数据
        conn.query(`SELECT * FROM ${tableName} LIMIT ${offset}, ${splitNumber}`,
          (err, results) => {
            if (err) {
              conn.rollback();
              bar.stop();
              return callback(err);
            }

            if (results.length === 0) {
              bar.stop();
              return callback();
            }

            // 2. 导出到Excel
            writeExcel(results, `${tableName}_part${currentBatch + 1}`);

            // 3. 获取要删除的ID范围
            const ids = results.map(row => row.id);
            const minId = Math.min(...ids);
            const maxId = Math.max(...ids);

            // 4. 删除已导出的数据
            conn.query(`DELETE FROM ${tableName} WHERE id BETWEEN ? AND ?`,
              [minId, maxId], (err) => {
                if (err) {
                  conn.rollback();
                  bar.stop();
                  return callback(err);
                }

                conn.commit(err => {
                  if (err) {
                    conn.rollback();
                    bar.stop();
                    return callback(err);
                  }

                  bar.update(++currentBatch);
                  if (currentBatch < batchCount) {
                    processBatch();
                  } else {
                    bar.stop();
                    callback();
                  }
                });
              });
          });
      });
    }

    processBatch();
  });
}

(function () {
  const conn = mysql.createConnection(dbConfig);
  conn.connect((err) => {
    if (err) {
      console.error('数据库连接失败:', err);
      return;
    }
    console.log('数据库连接成功');
    exportAndDelete((err) => {
      if (err) console.error(err);
      else console.log('所有数据导出并删除完成');
      conn.end();
    }, conn);
  });
})();
